package com.cn.ccp.service.course;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import net.sf.json.JSONObject;

import org.jfree.util.Log;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.bean.TrainingBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;

public class TrainingService extends BasicService {
	
	public int getCount(String chapter_course_id){
		String sql = "SELECT COUNT(1) FROM training WHERE chapter_course_id = ?";
		return jdbcTemplate.queryForInt(sql , new Object[]{chapter_course_id});
	}

	public boolean sava(TrainingBean bean){
		String sql = "INSERT INTO training "+
					" (chapter_course_id,training_no,training_contents, "+ 
					" training_options,training_refrence_answer, "+ 
					" training_score,is_chapter)VALUES(?,?,?,?,?,?,?)";
		Object[] objs = new Object[]{bean.getChapter_course_id() ,bean.getTraining_no() ,bean.getTraining_contents(),
									bean.getTraining_options() ,bean.getTraining_refrence_answer() ,
									bean.getTraining_score() ,bean.getIs_chapter()};
		return jdbcTemplate.update(sql ,objs)>0 ;
	}
	
	public boolean update(TrainingBean bean){
		String sql = "update training set "+
					" chapter_course_id = ? ,training_no = ? ,training_contents = ? , "+ 
					" training_options = ? ,training_refrence_answer = ? , "+ 
					" training_score = ? ,is_chapter = ? where id = ?";
			Object[] objs = new Object[]{bean.getChapter_course_id() ,bean.getTraining_no() ,bean.getTraining_contents(),
						bean.getTraining_options() ,bean.getTraining_refrence_answer() ,
						bean.getTraining_score() ,bean.getIs_chapter() , bean.getId()};
		return jdbcTemplate.update(sql ,objs)>0 ;
	}
	
	public boolean delete(final int id){
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql = "SELECT training_no FROM training WHERE id = ?";
					int training_no  = (Integer) jdbcTemplate.queryForObject(sql,new Object[]{id} ,Integer.class);
					
					sql = "delete from training where id = ?";
					jdbcTemplate.update(sql ,new Object[]{id}) ;
					
					sql = "UPDATE training SET training_no  = training_no-1 WHERE training_no > ? ";
					jdbcTemplate.update(sql ,new Object[]{training_no}) ;
					
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					e.printStackTrace();
					CCPContants.tranmitResultMap.put(index, 0);
				}
				return null;
			}
		});
		return  (Boolean) CCPContants.tranmitResultMap.get(index) ;
	}
	
	public void goDown(final String id , final String type){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					TrainingBean bean = showBean(id);
					int training_no  = Integer.parseInt(bean.getTraining_no());
					int count = getCount(bean.getChapter_course_id());
					if(training_no < count){
						String sql = "select id from training where training_no = ? and chapter_course_id = ? and is_chapter = ?";
						int upId = (Integer) jdbcTemplate.queryForObject(sql, new Object[]{training_no+1 ,bean.getChapter_course_id(), type} , Integer.class);
						
						
						sql = "UPDATE training SET training_no  = training_no-1 WHERE id = ? ";
						jdbcTemplate.update(sql,new Object[]{upId});
						
						sql = "UPDATE training SET training_no  = training_no+1 WHERE id = ? ";
						jdbcTemplate.update(sql,new Object[]{bean.getId()});
					}
					

				} catch (Exception e) {
					e.printStackTrace();
				}
				return null;
			}
		});
	}
	public void goUp(final String id ,final String type ){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					TrainingBean bean = showBean(id);
					int training_no  = Integer.parseInt(bean.getTraining_no());
					if(training_no > 1){
						String sql = "select id from training where training_no = ? and chapter_course_id = ? and is_chapter = ?";
						int upId = (Integer) jdbcTemplate.queryForObject(sql, new Object[]{training_no-1 ,bean.getChapter_course_id() ,type} , Integer.class);
						
						
						sql = "UPDATE training SET training_no  = training_no+1 WHERE id = ? ";
						jdbcTemplate.update(sql,new Object[]{upId});
						
						sql = "UPDATE training SET training_no  = training_no-1 WHERE id = ? ";
						jdbcTemplate.update(sql,new Object[]{bean.getId()});
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
				return null;
			}
		});
	}
	
	/**
	 * 删除考试试题
	 * @param id
	 */
	public void doDeleteTest(final String id){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					 
						String sql = "delete from training where chapter_course_id = ? and is_chapter = 1";
						jdbcTemplate.update(sql,new Object[]{id});
						 
						sql = "delete from course_setting WHERE id = ? ";
						jdbcTemplate.update(sql,new Object[]{id});
					 
				} catch (Exception e) {
					Log.error("delete test failed : ", e);
				}
				return null;
			}
		});
	}
	
	
	
	
	public TrainingBean showBean(String id){
		String sql = "SELECT * FROM training t  where t.id = ?";
		Object[] objs = new Object[]{id};
		return (TrainingBean) jdbcTemplate.queryForObject(sql, objs ,new TrainingRowMapper()) ;
	}
	
	
	public List<TrainingBean> getCourseAll(String chapter_course_id ,String type ){
		String sql = "SELECT t.* ,c.courses_name ,c.total_score FROM training t "+
				" LEFT OUTER JOIN courses c ON c.id = t.chapter_course_id "+ 
				" WHERE  t.chapter_course_id = ? and t.is_chapter = ?  ORDER BY training_no asc";
		Object[] objs = new Object[]{chapter_course_id ,type};
		return jdbcTemplate.query(sql, objs ,new TrainingRowMapper()) ;
	}
	
	public List<TrainingBean> getChapterAll(String chapter_course_id){
		String sql = "SELECT t.* ,cd.chapter_title ,cd.total_score FROM training t "+
						" LEFT OUTER JOIN courses_detail cd ON cd.id = t.chapter_course_id "+
						" WHERE t.is_chapter = 2 and t.chapter_course_id = ?  ORDER BY t.training_no asc";
		Object[] objs = new Object[]{chapter_course_id};
		return jdbcTemplate.query(sql, objs ,new TrainingRowMapper()) ;
	}
	
	//检查成绩
	public TraningScoreBean checkTraningScoreIsSubmit(String isChapter , String cid){
		String sql = "";
		Object[] objs = new Object[]{cid};
		sql = "SELECT cs.score AS total_score , SUM(t.training_score) AS training_score FROM course_setting cs "+
			    " LEFT JOIN training t ON t.chapter_course_id = cs.id "+
			    " WHERE cs.id =  ?";
		return (TraningScoreBean) jdbcTemplate.queryForObject(sql, objs, new TraningScoreRowMapper());
	}
	
	
	
	public class TraningScoreRowMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			TraningScoreBean bean = new TraningScoreBean();
			try {
				bean.setTotalScore(rs.getInt("total_score"));
			} catch (Exception e) {
				// TODO: handle exception
			}
			try {
				bean.setTraningScore(rs.getInt("training_score"));
			} catch (Exception e) {
				bean.setTraningScore(0);
			}
			return bean;
		}
	}
	
	public class TrainingRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			TrainingBean bean = new TrainingBean();
			bean.setId(rs.getInt("id"));
			bean.setIs_chapter(rs.getString("is_chapter"));
			bean.setChapter_course_id(rs.getString("chapter_course_id"));
			
			if(bean.getIs_chapter().equals("0")){
//				String courseName = BaseAction.getInstance().getCourseService().getBeanById(bean.getChapter_course_id()).getCourse_name() ;
//				bean.setCourseName(courseName);
				try {
					bean.setCourseName(rs.getString("courses_name"));
					bean.setTotal_score(rs.getString("total_score"));
				} catch (Exception e) {
				}
			}else if(bean.getIs_chapter().equals("1")){
//				String chapterName = BaseAction.getInstance().getCourseContentService().showBean(bean.getChapter_course_id()).getChapter_title();
//				bean.setCourseName(chapterName);
				try {
					bean.setChapterName(rs.getString("chapter_title"));
					bean.setTotal_score(rs.getString("total_score"));
				} catch (Exception e) {
					// TODO: handle exception
				}
			}
			bean.setTraining_no(rs.getString("training_no"));
			bean.setTraining_contents(rs.getString("training_contents"));
			bean.setTraining_options(rs.getString("training_options"));
			bean.setTraining_refrence_answer(rs.getString("training_refrence_answer"));
			bean.setTraining_score(rs.getString("training_score"));
			// 进行选择项类型解析
			String selectOption = bean.getTraining_options();
			/**
			 * 表示当前操作是判断题还是选择题
			 * 判断题标示： 1：[选择值0：错误/1：正确]
			 * 选择题标示：0：[A值描述]:[B值描述]:[C值描述]:[D值描述]
			 */
			JSONObject json = JSONObject.fromObject(selectOption);
			
			bean.setTraining_options_type(json.getString("type"));
			if(bean.getTraining_options_type().equals("1")){//判断题
				bean.setTraining_options_judgment(bean.getTraining_refrence_answer());
			}else{
				bean.setTraining_options_select_a(json.getString("a"));
				bean.setTraining_options_select_b(json.getString("b"));
				bean.setTraining_options_select_c(json.getString("c"));
				bean.setTraining_options_select_d(json.getString("d"));
				
				
				if(bean.getTraining_options_select_a() == null  || bean.getTraining_options_select_a().equals(""))
					bean.setHiddenA(true);
				if(bean.getTraining_options_select_b() == null  || bean.getTraining_options_select_b().equals(""))
					bean.setHiddenB(true);
				if(bean.getTraining_options_select_c() == null  || bean.getTraining_options_select_c().equals(""))
					bean.setHiddenC(true);
				if(bean.getTraining_options_select_d() == null  || bean.getTraining_options_select_d().equals(""))
					bean.setHiddenD(true);
				
				
			}
			
			
//			String[] strs = selectOption.split(":");
//			bean.setTraining_options_type(strs[0]);
//			if(bean.getTraining_options_type().equals("1")){//判断题
//				bean.setTraining_options_judgment(strs[1]);
//			}else{
//				bean.setTraining_options_select_a(strs[1]);
//				bean.setTraining_options_select_b(strs[2]);
//				bean.setTraining_options_select_c(strs[3]);
//				bean.setTraining_options_select_d(strs[4]);
//			}
			return bean;
		}
	};
	
	public class TraningScoreBean{
		private int totalScore ;
		private int traningScore ;
		public int getTotalScore() {
			return totalScore;
		}
		public void setTotalScore(int totalScore) {
			this.totalScore = totalScore;
		}
		public int getTraningScore() {
			return traningScore;
		}
		public void setTraningScore(int traningScore) {
			this.traningScore = traningScore;
		}
	}
	
	
}
